package com.mercenary.wein.screenControl.dao;

import com.mercenary.wein.screenControl.bean.DeviceBean;
import com.mercenary.wein.screenControl.bean.DeviceGroupBean;
import com.mercenary.wein.screenControl.bean.GroupMetaBean;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by tuxiao on 2017/3/11.
 */
public class GroupDao {
    Log log = LogFactory.getLog(GroupDao.class);

    public GroupMetaBean createGroupMeta(String gname, String comment){
        String sql = "insert into groupmeta values (null,?,?)";
        log.info("create group:"+sql.replace("?",gname).replace("?",comment));
        Connection connection = ConnectDb.Connect();
        QueryRunner queryRunner = new QueryRunner();
        try {
            long id = queryRunner.insert(connection,sql,new ScalarHandler<Long>(),gname,comment);
            DbUtils.close(connection);
            return new GroupMetaBean(id,gname,comment);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<GroupMetaBean> getGroups(){
        String sql = "select * from groupmeta";
        log.info("get group");
        Connection connection = ConnectDb.Connect();
        QueryRunner queryRunner = new QueryRunner();
        try {
            List<GroupMetaBean> results = queryRunner.query(connection, sql,new BeanListHandler<GroupMetaBean>(GroupMetaBean.class));
            DbUtils.close(connection);
            return results;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void insertDeviceGroup(int gcode, String serialNo){
        String sql = "insert into devicegroup values (null,?,?,'')";
        log.info("create group:"+sql.replaceAll("\\?",gcode+"").replaceAll("\\?",serialNo));
        Connection connection = ConnectDb.Connect();
        QueryRunner queryRunner = new QueryRunner();
        try {
            queryRunner.update(connection,sql,gcode,serialNo);
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateDeviceGroup(long gcode, String serialNo){
        String sql = "update devicegroup set gcode=? where did=?";
        log.info("update group:"+sql.replaceAll("\\?",gcode+"").replaceAll("\\?",serialNo));
        Connection connection = ConnectDb.Connect();
        QueryRunner queryRunner = new QueryRunner();
        try {
            queryRunner.update(connection,sql,gcode,serialNo);
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<DeviceBean> findDevicesByGroupId(long groupId){
        String sql = "select t.*,p.gcode from device t join devicegroup p on t.serialNo=p.did where p.gcode=? and status=0";
        log.info("find ports by groupId :"+sql.replace("?",groupId+""));
        Connection connection = ConnectDb.Connect();
        QueryRunner queryRunner = new QueryRunner();
        try {
            List<DeviceBean> results = queryRunner.query(connection,sql,new BeanListHandler<DeviceBean>(DeviceBean.class),groupId);
            DbUtils.close(connection);
            return results;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<DeviceBean> findDevicesGroups(){
        String sql = "select t.*,p.gcode from device t join devicegroup p on t.serialNo=p.did where status=0";
        log.info("find ports by groupId :"+sql);
        Connection connection = ConnectDb.Connect();
        QueryRunner queryRunner = new QueryRunner();
        try {
            List<DeviceBean> results = queryRunner.query(connection,sql,new BeanListHandler<DeviceBean>(DeviceBean.class));
            DbUtils.close(connection);
            return results;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public String getGroupName(long gcode){
        String sql = "select * from groupmeta where gcode=?";
        log.info("getGroupName :"+sql.replace("?",gcode+""));
        Connection connection = ConnectDb.Connect();
        QueryRunner queryRunner = new QueryRunner();
        try {
            List<GroupMetaBean> results = queryRunner.query(connection,sql,new BeanListHandler<GroupMetaBean>(GroupMetaBean.class),gcode);
            DbUtils.close(connection);
            if(results.size()==0)return null;
            return results.get(0).getGname();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
